package com.store;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jedi.BaseObj;
import com.tss.util.DbConn;
import com.tss.util.DbRs;
public class TaskMgr extends BaseObj {
	public Task get(String id) {
		clearErr();
		Task task = new Task();
		Contract contract = new Contract();
		DbConn conn = new DbConn();
		try {
			String sql = "";
			sql = "select t.* ,COALESCE(t.aenddate - t.abegindate + 1, 0) * 8 + t.worktime AS normaltime,(COALESCE(t.aenddate - t.abegindate + 1, 0) * 8 + t.worktime) / 8 AS totdays ,u2.user_name as techmanager_name,u.user_id,u.user_name as engineername,u1.user_name as scoreman_name  from task t " +
			"left join userinfo u on u.user_id=t.engineerid  " +
			"left join userinfo u1 on u1.user_id=t.scoreman  " +
			"left join techcontract tc on tc.conid=t.conid  " +
			"left join userinfo u2  on tc.techmanager=u2.user_id  " +
			
					" where t.taskid = ?";
			conn.prepare(sql);
			conn.setString(1,id);
			DbRs rs = conn.executeQuery();
			if (rs == null || rs.size() ==0) return null;
			task.setAddr(get(rs,0,"addr"));
			task.setArea_id(get(rs,0,"area_id"));
			task.setBegindate(getDate(rs,0,"begindate"));
			task.setConid(getInt(rs,0,"conid"));
			task.setCreatetime(getDate(rs,0,"createtime"));
			task.setCusname(get(rs,0,"cusname"));
			task.setDiffmod(getInt(rs,0,"diffmod"));
			task.setEnddate(getDate(rs,0,"enddate"));
			task.setEngineerid(get(rs,0,"engineerid"));
			task.setLinkman(get(rs,0,"linkman"));
			task.setMatterinfo(get(rs,0,"matterinfo"));
			task.setNormaltime(getInt(rs,0,"normaltime"));
			task.setPhone(get(rs,0,"phone"));
			task.setScoreman(get(rs,0,"scoreman"));
			task.setScoretime(getDate(rs,0,"scoretime"));
			task.setServicelog(get(rs,0,"servicelog"));
			task.setStatus(getInt(rs,0,"status"));
			task.setTaskaddress(get(rs,0,"taskaddress"));
			task.setSpartfee(getDouble(rs,0,"spartfee"));
			task.setOutefee(getDouble(rs,0,"outefee"));
			task.setTaskid(get(rs,0,"taskid"));
			task.setTaskscore(getInt(rs,0,"taskscore"));
			task.setTasktime(getInt(rs,0,"totdays"));
			task.setTripfee(getDouble(rs,0,"tripfee"));
			task.setTriptype(getInt(rs,0,"triptype"));
			task.setWorktime(getInt(rs,0,"worktime"));	
			task.setAbegindate(getDate(rs,0,"abegindate"));
			task.setAenddate(getDate(rs,0,"aenddate"));
			task.setEngineername(get(rs,0,"engineername"));
			task.setScoreman(get(rs,0,"scoreman_name"));
			task.setTasklile(get(rs,0,"tasklile"));
			
			contract.setTechmanager_name(get(rs,0,"techmanager_name"));
			task.setContract(contract);
			sql = "select * from taskdetail where taskid = ? order by partid ";
			conn.prepare(sql);
			conn.setString(1,id);
			rs = conn.executeQuery();
			if (rs != null && rs.size() > 0) {
				List list = new ArrayList();
				for(int i=0;i<rs.size();i++) {
					TaskDetail detail = new TaskDetail(get(rs,i,"partid"));
					detail.setTaskId(getInt(rs,i,"taskid"));
					detail.setRevPartNo(get(rs,i,"revpartno"));
					detail.setSerialNo(get(rs,i,"serialno"));
					detail.setContextFile(get(rs,i,"contextfile"));
					detail.setContextDesc(get(rs,i,"contextdesc"));
					detail.setOutPartNo(get(rs,i,"outpartno"));
					detail.setOutSerialNo(get(rs,i,"outserialno"));
					list.add(detail);
				}
				task.setListDetail(list);
			}
			
			return task;
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
	}
	public List get(String otherSQL,String orderField) {
		clearErr();
		DbConn conn = new DbConn();
		DbRs rs = null;
		try {
			String sql = "";
			sql = "select * from task where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals("")) 
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery();
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
		if (rs != null && rs.size() >0) {
			List r = new ArrayList();
			for(int i=0;i<rs.size();i++) {
				Task task = new Task(get(rs,i,"taskid"));
				task.setAddr(get(rs,i,"addr"));
				task.setArea_id(get(rs,i,"area_id"));
				task.setBegindate(getDate(rs,i,"begindate"));
			
				task.setConid(getInt(rs,i,"conid"));
				task.setCreatetime(getDate(rs,i,"createtime"));
				task.setCusname(get(rs,i,"cusname"));
				task.setDiffmod(getInt(rs,i,"diffmod"));
				task.setEnddate(getDate(rs,i,"enddate"));
				task.setEngineerid(get(rs,i,"engineerid"));
				task.setLinkman(get(rs,i,"linkman"));
				task.setMatterinfo(get(rs,i,"matterinfo"));
				task.setNormaltime(getInt(rs,i,"normaltime"));
			
				task.setPhone(get(rs,i,"phone"));
			
			
				task.setScoreman(get(rs,i,"scoreman"));
				task.setScoretime(getDate(rs,i,"scoretime"));
				task.setServicelog(get(rs,i,"productsernum"));
			
				task.setStatus(getInt(rs,i,"status"));
				task.setTaskaddress(get(rs,i,"taskaddress"));
				task.setSpartfee(getDouble(rs,i,"spartfee"));
				task.setOutefee(getDouble(rs,i,"outefee"));
				task.setTaskid(get(rs,i,"taskid"));
				task.setTaskscore(getInt(rs,i,"taskscore"));
				task.setTasktime(getInt(rs,i,"tasktime"));
				task.setTripfee(getDouble(rs,i,"tripfee"));
				task.setTriptype(getInt(rs,i,"triptype"));
				task.setWorktime(getInt(rs,i,"worktime"));		
				task.setAbegindate(getDate(rs,i,"abegindate"));
				task.setAenddate(getDate(rs,i,"aenddate"));
				r.add(task);
			}
			return r;
		}
		return null;
	}
	
	public List get(String otherSQL,String orderField,int pageNo, int pageSize) {
		clearErr();
		DbRs rs = null;
		DbConn conn = null;
		try {
			conn = new DbConn();
			String sqlCount = "select count(*) as total from task t  " +
			"left join userinfo u on u.user_id=t.engineerid " +
			"left join techcontract tc on tc.conid=t.conid  " +
				"where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sqlCount += otherSQL;
			conn.prepare(sqlCount);
			DbRs rsCount = conn.executeQuery();
			int total = getInt(rsCount,0,"total");
			setTotal(total);
			boolean b = total % pageSize == 0;
			int pageCount = b ? (total / pageSize) : (total / pageSize + 1);
			if (pageNo > pageCount) pageNo = pageCount;
			String sql = "select t.*,u2.user_name as techmanager_name,u.user_id,u.user_name as engineername from task t " +
			"left join userinfo u on u.user_id=t.engineerid " +
			"left join techcontract tc on tc.conid=t.conid  " +
			"left join userinfo u2  on tc.techmanager=u2.user_id  " +
					" where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery(pageNo, pageSize);
		} catch (SQLException ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			setErr(e.getMessage());
			return new ArrayList();
		} finally {
			conn.close();
		}
		List r = new ArrayList();
		if (rs == null || rs.size() <= 0)
			return r;
		for (int i = 0; i < rs.size(); i++) {
			Contract contract = new Contract();
			Task task = new Task(get(rs,i,"taskid"));
			task.setAddr(get(rs,i,"addr"));
			task.setArea_id(get(rs,i,"area_id"));
			task.setBegindate(getDate(rs,i,"begindate"));
			task.setConid(getInt(rs,i,"conid"));
			task.setCreatetime(getDate(rs,i,"createtime"));
			task.setCusname(get(rs,i,"cusname"));
			task.setDiffmod(getInt(rs,i,"diffmod"));
			task.setEnddate(getDate(rs,i,"enddate"));
			task.setEngineerid(get(rs,i,"engineerid"));
			task.setLinkman(get(rs,i,"linkman"));
			task.setMatterinfo(get(rs,i,"matterinfo"));
			task.setNormaltime(getInt(rs,i,"normaltime"));
			task.setPhone(get(rs,i,"phone"));
			task.setScoreman(get(rs,i,"scoreman"));
			task.setScoretime(getDate(rs,i,"scoretime"));
			task.setServicelog(get(rs,i,"servicelog"));
			task.setStatus(getInt(rs,i,"status"));
			task.setTaskaddress(get(rs,i,"taskaddress"));
			task.setSpartfee(getDouble(rs,i,"spartfee"));
			task.setOutefee(getDouble(rs,i,"outefee"));
			task.setTaskid(get(rs,i,"taskid"));
			task.setTaskscore(getInt(rs,i,"taskscore"));
			task.setTasktime(getInt(rs,i,"tasktime"));
			task.setTripfee(getDouble(rs,i,"tripfee"));
			task.setTriptype(getInt(rs,i,"triptype"));
			task.setWorktime(getInt(rs,i,"worktime"));
			task.setAbegindate(getDate(rs,i,"abegindate"));
			task.setAenddate(getDate(rs,i,"aenddate"));
			task.setEngineername(get(rs,i,"engineername"));
			contract.setTechmanager_name(get(rs,i,"techmanager_name"));
			task.setContract(contract);
		
			r.add(task);
		}
		return r;
	}

}
